package com.liwen.kefu;

import com.liwen.kefu.service.ActionDataService;
import com.liwen.kefu.service.InitDataService;
import com.liwen.kefu.utils.JdbcUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.CollectionUtils;

import java.io.IOException;
import java.sql.SQLException;
import java.time.Duration;
import java.time.Instant;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: Liwen
 * @createTime: 2024/11/1
 * @description:
 */

@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class BaseTableAction {

    @Autowired
    private ActionDataService actionDataService;
    @Autowired
    private InitDataService initDataService;
    String userId = "";
    String modelName = "会员订单-提速卡+优惠券-";
    String type = "emc";
    String mobileNo = "13547753937";
    String path = "E:\\gmf\\测试\\7.客服\\9.会员列表_新增放款时长+使用了提速卡的借款单号\\sql\\4-无提速卡+无优惠券\\4-13547753937";

    /**
     * 根据手机号查询userId
     */
    @Before
    public void queryUserId() {
        userId = actionDataService.execHiveQueryUserId(mobileNo, type);
    }


    /**
     * 批量查询《新、老核心、会员》基础表 导入mysql
     */
    @Test
    public void baseTableAll() {
        Instant start = Instant.now();
        Map<String, String> baseTableMap = initDataService.initMysqlTableName(type);
        Map<String, String> successMap = new LinkedHashMap<>();
        Map<String, String> failMap = new LinkedHashMap<>();
        final int[] num = {0};
        baseTableMap.forEach((tableName, dbName) -> {
            try {
                Thread.sleep(2000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            String msg = "";
            num[0]++;
            String sql = initDataService.initQuerySql(userId, mobileNo, tableName);
            String fileName = num[0] + "." + modelName + mobileNo + "-" + tableName;
            String filePath = path + "\\" + fileName + ".xlsx";
            log.info("第：{}次，hive 查询sql：{}", num, sql);
            log.info(filePath);
            List<Map<String, Object>> list = actionDataService.execHiveQuery(sql, tableName);
            if (CollectionUtils.isEmpty(list)) {
                msg = "{}，hive 查询结果=null";
                log.error(msg, tableName);
                failMap.put(fileName, msg);
                num[0]--;
                return;
            }
            actionDataService.exportDataToExcel(filePath, list);

            try {
                msg = actionDataService.excelImportDataToMysql(dbName, tableName, filePath);
                successMap.put(fileName, msg);
            } catch (Exception e) {
                e.printStackTrace();
                failMap.put(fileName, msg);
            }

        });
        log.info("【本次基础表应导入:{}张表，成功：{},失败：{}】", baseTableMap.size(), successMap.size(), failMap.size());
        System.out.println();
        if (!CollectionUtils.isEmpty(failMap)) {
            log.error("【导入失败：{}】=====================================================", failMap.size());
            failMap.forEach((k, v) -> {
                System.out.println(k + ":" + v);
            });
        }
        log.info("【导入成功：{}】=====================================================", successMap.size());
        successMap.forEach((k, v) -> {
            System.out.println(k + " : " + v);
        });
        JdbcUtil.close();
        Instant finish = Instant.now();
        long timeElapsed = (Duration.between(start, finish).toMillis()) / 1000;
        log.info("总耗时：{}s", timeElapsed);

    }


    /**
     * 批量删除《新、老核心、会员》基础表
     */
    @Test
    public void deleteMysqlBatch() {
        Map<String, String> baseTableMap = initDataService.initMysqlTableName(type);
        final int[] num = {0};
        Map<String, String> successMap = new LinkedHashMap<>();
        Map<String, String> failMap = new LinkedHashMap<>();
        baseTableMap.forEach((tableName, dbName) -> {
            try {
                Thread.sleep(2000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            String msg = "success";
            num[0]++;
            String fileName = num[0] + "." + modelName + mobileNo + "-" + tableName;
            String filePath = path + "\\" + fileName + ".xlsx";
            log.info(filePath);
            String key = initDataService.initKeys(dbName, tableName);
            String value = "";
            try {
                value = actionDataService.getDeleteKeysValue(filePath, key);

            } catch (IOException e) {
                num[0]--;
                e.printStackTrace();
                failMap.put(fileName, "文件未找到！");
                return;
            }
            try {
                log.info("【第：{}次删除：{}】===============================", num, tableName);
                actionDataService.execSqlDelete(dbName, tableName, key, value);
                successMap.put(fileName, msg);
            } catch (SQLException e) {
                e.printStackTrace();
                failMap.put(fileName, msg);
                return;
            }
        });
        log.info("【本次基础表应删除:{}张表，成功：{},失败：{}】", num, successMap.size(), failMap.size());
        if (!CollectionUtils.isEmpty(failMap)) {
            log.error("【删除失败：{}】=====================================================", failMap.size());
            failMap.forEach((k, v) -> {
                System.out.println(k + ":" + v);
            });
        }
        log.error("【删除成功：{}】=====================================================", successMap.size());
        successMap.forEach((k, v) -> {
            System.out.println(k + " : " + v);
        });
        JdbcUtil.close();
    }


    /**
     * 《会员模块》基础表数据导入
     */
    @Test
    public void baseEmcTest() {
        Instant start = Instant.now();
        Map<String, String> baseTableMap = initDataService.initMysqlTableName(type);
        Map<String, String> successMap = new LinkedHashMap<>();
        Map<String, String> failMap = new LinkedHashMap<>();
        final int[] num = {0};
        baseTableMap.forEach((tableName, dbName) -> {
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            String msg = "";
            num[0]++;
            String sql = initDataService.initQuerySql(userId, mobileNo, tableName);
            String fileName = num[0] + "." + modelName + mobileNo + "-" + tableName;
            String filePath = path + "\\" + fileName + ".xlsx";
            log.info("第：{}次，hive 查询sql：{}", num, sql);
            log.info(filePath);
            List<Map<String, Object>> list = actionDataService.execHiveQuery(sql, tableName);
            if (CollectionUtils.isEmpty(list)) {
                msg = "{}，hive 查询结果=null";
                log.error(msg, tableName);
                failMap.put(fileName, msg);
                num[0]--;
                return;
            }
            actionDataService.exportDataToExcel(filePath, list);

            try {
                msg = actionDataService.excelImportDataToMysql(dbName, tableName, filePath);
                successMap.put(fileName, msg);
            } catch (Exception e) {
                e.printStackTrace();
                failMap.put(fileName, msg);
            }

        });
        log.info("【本次基础表应导入:{}张表，成功：{},失败：{}】", baseTableMap.size(), successMap.size(), failMap.size());
        System.out.println();
        if (!CollectionUtils.isEmpty(failMap)) {
            log.error("【导入失败：{}】=====================================================", failMap.size());
            failMap.forEach((k, v) -> {
                System.out.println(k + ":" + v);
            });
        }
        log.error("【导入成功：{}】=====================================================", successMap.size());
        successMap.forEach((k, v) -> {
            System.out.println(k + " : " + v);
        });
        JdbcUtil.close();
        Instant finish = Instant.now();
        long timeElapsed = (Duration.between(start, finish).toMillis()) / 1000;
        log.info("总耗时：{}s", timeElapsed);

    }


}
